import path from "node:path";
import { access, constants, mkdir } from 'node:fs/promises'
import sqlite from "sqlite3"
const sqlite3 = sqlite.verbose();

const staticPath = path.resolve(process.cwd(), "./db");
access(staticPath, constants.F_OK).catch(err => mkdir(staticPath))

export default class SQLiteDB {
  db: any = null;

  constructor(private name = "base") {
    this.db = new sqlite3.Database(
      path.resolve(process.cwd(), `db/${name}.db`),
      (err) => {
        if (err) console.log(err);
      }
    );
  }

  createTable(tableName, columns: any[]) {
    const columnDefinitions = columns
      .map((column) => `${column.name} ${column.type}`)
      .join(", ");
    const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions})`;
    this.db.run(query);
  }

  addColumns(tableName, columns: Array<{ name: string, type: 'NULL' | 'INTEGER' | 'REAL' | 'TEXT'  | 'BLOB' }>) {
      this.db.serialize(() => {
        columns.forEach(col => {
          this.db.run(`ALTER TABLE ${tableName} ADD COLUMN ${col.name} ${col.type};`);
        })
      })
  }

  insertData(tableName, data) {
    const columns = Object.keys(data).join(", ");
    const placeholders = Object.keys(data)
      .map(() => "?")
      .join(", ");
    const values = Object.values(data);
    const query = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
    this.db.run(query, values);
  }

  updateData(tableName, data, condition) {
    const setClause = Object.keys(data)
      .map((column) => `${column} = ?`)
      .join(", ");
    const values = Object.values(data);
    const query = `UPDATE ${tableName} SET ${setClause} WHERE ${condition}`;
    this.db.run(query, values);
  }

  deleteData(tableName, condition) {
    const query = `DELETE FROM ${tableName} WHERE ${condition}`;
    this.db.run(query);
  }

  executeQuery(query, params, callback) {
    this.db.all(query, params, (err, rows) => {
      if (err) {
        console.error(err);
      } else {
        callback(rows);
      }
    });
  }

  /**
   * 获取分页数据 *
   * @param tableName
   * @param page
   * @param pageSize
   * @param condition // const condition = "column_name = 'value'";
   * @returns {Promise<unknown>}
   */
  getPagedData(tableName, page, pageSize, condition) {
    const offset = (page - 1) * pageSize;
    const countQuery = `SELECT COUNT(*) as total FROM ${tableName} WHERE ${condition}`;
    const dataQuery = `SELECT * FROM ${tableName} WHERE ${condition} LIMIT ? OFFSET ?`;
    return new Promise((resolve, reject) => {
      // serialize 顺序执行
      this.db.serialize(() => {
        this.db.get(countQuery, (err, row) => {
          if (err) {
            reject(err);
          } else {
            const total = row.total;
            this.db.all(dataQuery, [pageSize, offset], (err, rows) => {
              if (err) {
                reject(err);
              } else {
                const totalPages = Math.ceil(total / pageSize);
                resolve({ data: rows, total, totalPages });
              }
            });
          }
        });
      });
    });
  }

  closeConnection() {
    this.db.close();
  }
}
